This disclosure relates generally to database management systems, and more particularly to a method and system for bundled preparation and execution of multiple database statements.
Structured Query Language (SQL) is a language for managing data in database management systems (DBMSs), and an important language element of SQL is the SQL statement. Database client (DB client) software provides database client applications with a database client application programming interface (API) for accessing, manipulating, and managing data in the DBMS. A client application (together with the DB client) and a database potentially run on different interconnected computer systems in a heterogeneous computer system landscape. Object oriented DB client APIs often provide a statement class or a similar concept which embodies the semantics of an executable SQL statement.
A family of DB client APIs, for example open database connectivity (ODBC) or Java database connectivity (JDBC), provides a mechanism to establish connections to a database and interact with a connected database by means of SQL statements. The DB client API allows client applications to send an SQL statement, potentially together with input data, to the database system where it is executed by the DBMS. After execution of the SQL statement, output data is transferred back to the client DB system, where it is accessible by means provided by the DB client API.
Each DB client application API has to prepare a set of SQL statements P={p1, p2, . . . pm} for later execution as well as to execute a set of statements S={s1, s2, . . . sn}. Moreover, dependencies between the elements of P and S may exist, meaning that each statement (in the union U of P and S) potentially depends on one or more other statements (elements of U). However, the dependencies always result in a directed acyclic dependency graph.
The preparation and execution of all those statements leads to a number of roundtrips between client application and database. Since the number of roundtrips has a significant impact on the overall performance, there are scenarios in which the preparation of multiple statements in conjunction with the additional execution of statements imposes a significant negative effect on the overall performance, especially when data transport over a network is involved.